﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Xml.Linq;

namespace ExcelRead
{
    class Program
    {
        static void Main(string[] args)
        {
            //XElement xe = new XElement("Table");
            var ds = LoadDataFromExcel("Namelist.xls");
            var tb = ds.Tables[0];
            List<UserInfo> users = new List<UserInfo>();
            for (int r = 0; r < tb.Rows.Count; r++)
            {
                var row = tb.Rows[r];
                users.Add(new UserInfo()
                {
                    ChineseName = row[2].ToString(),
                    DisplayName = row[1].ToString(),
                    Email = row[0].ToString(),
                    TourBus = row[3].ToString(),
                    DepartureDate = row[4].ToString(),
                    DepartureTrip = row[5].ToString(),
                    DepartureTerminal = row[6].ToString(),
                    DepartureFlight = row[7].ToString(),
                    DepartureFlightLeader=row[8].ToString(),
                    ReturnDate = row[11].ToString(),
                    ReturnTrip = row[12].ToString(),
                    ReturnTerminal = row[13].ToString(),
                    ReturnFlight = row[14].ToString(),
                    ReturnFlightLeader = row[15].ToString(),
                    ShuttleHotelToAirport = row[16].ToString(),
                    RoommateName = row[18].ToString(),
                    HotelCheckOutTime = row[19].ToString()

                });
                //XElement rowXE = new XElement("Row");
                //for (int c = 0; c < tb.Columns.Count; c++)
                //{
                //    var column = row[c];
                //    rowXE.Add(new XElement(tb.Columns[c].ColumnName.Replace(" ", "_"), column.ToString()));
                //}
                //displayName.Add(row[1].ToString());
                //xe.Add(rowXE);
            }
            string str = SerializerHelp.JsonSerializer(users);
            //foreach (var name in displayName)
            //{
            //    foreach (var e in displayName)
            //    {
            //        if (name == e && displayName.IndexOf(name) != displayName.IndexOf(e))
            //        {

            //        }
            //    }
            //}
            //Console.WriteLine(xe.ToString());
            Console.WriteLine(str);
            using (var file = System.IO.File.Create("UserInfo.json"))
            {
                var buffer = System.Text.Encoding.UTF8.GetBytes(str);
                file.Write(buffer, 0, buffer.Length);
            }
            Console.ReadLine();
        }

        //加载Excel 
        public static DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [Grand$]";//可是更改Sheet名称，比如sheet2，等等 
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                Console.WriteLine("数据绑定Excel失败!失败原因：" + err.Message);
                return null;
            }
        }


        //public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
        //{
        //    Microsoft.Office.Interop.Excel.Application app =
        //        new Microsoft.Office.Interop.Excel.ApplicationClass();
        //    try
        //    {
        //        app.Visible = false;
        //        Workbook wBook = app.Workbooks.Add(true);
        //        Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
        //        if (excelTable.Rows.Count > 0)
        //        {
        //            int row = 0;
        //            row = excelTable.Rows.Count;
        //            int col = excelTable.Columns.Count;
        //            for (int i = 0; i < row; i++)
        //            {
        //                for (int j = 0; j < col; j++)
        //                {
        //                    string str = excelTable.Rows[i][j].ToString();
        //                    wSheet.Cells[i + 2, j + 1] = str;
        //                }
        //            }
        //        }

        //        int size = excelTable.Columns.Count;
        //        for (int i = 0; i < size; i++)
        //        {
        //            wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
        //        }
        //        //设置禁止弹出保存和覆盖的询问提示框 
        //        app.DisplayAlerts = false;
        //        app.AlertBeforeOverwriting = false;
        //        //保存工作簿 
        //        wBook.Save();
        //        //保存excel文件 
        //        app.Save(filePath);
        //        app.SaveWorkspace(filePath);
        //        app.Quit();
        //        app = null;
        //        return true;
        //    }
        //    catch (Exception err)
        //    {
        //        //MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
        //        //    MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        return false;
        //    }
        //    finally
        //    {
        //    }
        //}

    }


    [DataContract]
    public class UserInfo
    {
        [DataMember]
        public string ChineseName { get; set; }

        [DataMember]
        public string DisplayName { get; set; }

        [DataMember]
        public string Email { get; set; }

        [DataMember]
        public string DepartureTerminal { get; set; }

        [DataMember]
        public string DepartureFlightLeader { get; set; }

        [DataMember]
        public string DepartureTrip { get; set; }

        [DataMember]
        public string DepartureDate { get; set; }

        [DataMember]
        public string DepartureFlight { get; set; }

        [DataMember]
        public string ReturnTerminal { get; set; }

        [DataMember]
        public string ReturnTrip { get; set; }

        [DataMember]
        public string ReturnFlight { get; set; }

        [DataMember]
        public string ReturnFlightLeader { get; set; }

        [DataMember]
        public string ShuttleHotelToAirport { get; set; }

        [DataMember]
        public string RoommateName { get; set; }

        [DataMember]
        public string TourBus { get; set; }

        [DataMember]
        public string ReturnDate { get; set; }

        [DataMember]
        public string HotelCheckOutTime { get; set; }
    }
}
